BEGIN TRANSACTION
GO
ALTER TABLE dbo.VoucherDetails ADD
	VDID bigint NOT NULL CONSTRAINT DF_VoucherDetails_VDID DEFAULT 0
GO
COMMIT

-----------------------------------------------------------

USE [BSC]
GO
/****** Object:  View [dbo].[vw_VouchersDetail]    Script Date: 01/08/2020 13:56:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_VouchersDetail]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherNo, dbo.Voucher.VoucherDate, dbo.Voucher.Remarks AS MasterRemarks, ISNULL(dbo.Voucher.CustomerName, '') 
                      AS CustomerName, dbo.Voucher.GroupID AS SubPartyID, ISNULL(dbo.Groups.GroupName, '.') AS SubPartyName, dbo.Voucher.BankAccountID, 
                      dbo.Accounts.AccountTitle AS BankAccountTitle, dbo.Accounts.NameUrdu AS BankNameUrdu, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, 
                      dbo.Voucher.Amount AS SumAmount, dbo.Voucher.Expense, dbo.Voucher.Discount, dbo.Voucher.TotalAmount, dbo.Voucher.NetAmount, dbo.Voucher.Cash, 
                      dbo.Voucher.StoreID, dbo.Voucher.VehicleNo, dbo.Voucher.StoreLot, Accounts_1.NameUrdu, dbo.VoucherDetails.AccountNo, Accounts_1.AccountTitle, 
                      Accounts_1.ParentAccount, dbo.VoucherDetails.Remarks, dbo.VoucherDetails.Amount, dbo.VoucherDetails.Amount AS Debit, 0 AS Credit, dbo.VoucherDetails.DrOrCr, 
                      dbo.VoucherDetails.VoucherDetailID, dbo.vw_Items.GroupID, dbo.vw_Items.GroupCode, dbo.vw_Items.GroupName, dbo.vw_Items.GroupNameUrdu, 
                      dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, dbo.vw_Items.ItemNameUrdu, dbo.vw_Items.InUnit, dbo.VoucherDetails.Qty, 
                      dbo.VoucherDetails.Qty AS InQty, 0 AS OutQty, dbo.VoucherDetails.Rate, dbo.VoucherDetails.RoomID, dbo.Room.RoomCode, dbo.Room.RoomName, 
                      dbo.Room.RoomNameUrdu, dbo.VoucherDetails.RackID, dbo.Rack.RackCode, dbo.Rack.RackName, dbo.Rack.RackNameUrdu, dbo.VoucherDetails.ManzalID, 
                      dbo.Manzal.ManzalCode, dbo.Manzal.ManzalName, dbo.Manzal.ManzalNameUrdu, dbo.VoucherDetails.LotNo, dbo.VoucherDetails.Marka, 
                      dbo.VoucherDetails.AVDate, dbo.VoucherDetails.TotalDays, dbo.VoucherDetails.AdjustDays, dbo.VoucherDetails.NetDays, dbo.VoucherDetails.VDID
FROM         dbo.Groups RIGHT OUTER JOIN
                      dbo.Accounts RIGHT OUTER JOIN
                      dbo.Voucher ON dbo.Accounts.AccountNo = dbo.Voucher.BankAccountID ON dbo.Groups.GroupID = dbo.Voucher.GroupID RIGHT OUTER JOIN
                      dbo.Manzal RIGHT OUTER JOIN
                      dbo.VoucherDetails LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.VoucherDetails.ItemID = dbo.vw_Items.ItemID ON dbo.Manzal.ManzalID = dbo.VoucherDetails.ManzalID LEFT OUTER JOIN
                      dbo.Room ON dbo.VoucherDetails.RoomID = dbo.Room.RoomID LEFT OUTER JOIN
                      dbo.Rack ON dbo.VoucherDetails.RackID = dbo.Rack.RackID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON dbo.VoucherDetails.AccountNo = Accounts_1.AccountNo ON dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID
WHERE     (dbo.VoucherDetails.DrOrCr = N'Dr')
UNION ALL
SELECT     VoucherDetails_1.VoucherID, Voucher_1.VoucherNo, Voucher_1.VoucherDate, Voucher_1.Remarks AS MasterRemarks, ISNULL(Voucher_1.CustomerName, '') 
                      AS CustomerName, Voucher_1.GroupID AS SubPartyID, ISNULL(Groups_1.GroupName, '.') AS SubPartyName, Voucher_1.BankAccountID, 
                      Accounts_2.AccountTitle AS BankAccountTitle, Accounts_2.NameUrdu AS BankNameUrdu, Voucher_1.SessionID, SessionInfo_1.SessionTitle, 
                      Voucher_1.Amount AS SumAmount, Voucher_1.Expense, Voucher_1.Discount, Voucher_1.TotalAmount, Voucher_1.NetAmount, Voucher_1.Cash, Voucher_1.StoreID, 
                      Voucher_1.VehicleNo, VoucherDetails_1.StoreLot, Accounts_1.NameUrdu, VoucherDetails_1.AccountNo, Accounts_1.AccountTitle, Accounts_1.ParentAccount, 
                      VoucherDetails_1.Remarks, VoucherDetails_1.Amount, 0 AS Debit, VoucherDetails_1.Amount AS Credit, VoucherDetails_1.DrOrCr, VoucherDetails_1.VoucherDetailID, 
                      vw_Items_1.GroupID, vw_Items_1.GroupCode, vw_Items_1.GroupName, vw_Items_1.GroupNameUrdu, VoucherDetails_1.ItemID, vw_Items_1.ItemCode, 
                      vw_Items_1.ItemName, vw_Items_1.ItemNameUrdu, vw_Items_1.InUnit, VoucherDetails_1.Qty, 0 AS InQty, VoucherDetails_1.Qty AS OutQty, VoucherDetails_1.Rate, 
                      VoucherDetails_1.RoomID, Room_1.RoomCode, Room_1.RoomName, Room_1.RoomNameUrdu, VoucherDetails_1.RackID, Rack_1.RackCode, Rack_1.RackName, 
                      Rack_1.RackNameUrdu, VoucherDetails_1.ManzalID, Manzal_1.ManzalCode, Manzal_1.ManzalName, Manzal_1.ManzalNameUrdu, VoucherDetails_1.LotNo, 
                      VoucherDetails_1.Marka, VoucherDetails_1.AVDate, VoucherDetails_1.TotalDays, VoucherDetails_1.AdjustDays, VoucherDetails_1.NetDays, 
                      VoucherDetails_1.VDID
FROM         dbo.Groups AS Groups_1 RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_2 RIGHT OUTER JOIN
                      dbo.Voucher AS Voucher_1 ON Accounts_2.AccountNo = Voucher_1.BankAccountID ON Groups_1.GroupID = Voucher_1.GroupID RIGHT OUTER JOIN
                      dbo.Manzal AS Manzal_1 RIGHT OUTER JOIN
                      dbo.VoucherDetails AS VoucherDetails_1 LEFT OUTER JOIN
                      dbo.vw_Items AS vw_Items_1 ON VoucherDetails_1.ItemID = vw_Items_1.ItemID ON Manzal_1.ManzalID = VoucherDetails_1.ManzalID LEFT OUTER JOIN
                      dbo.Room AS Room_1 ON VoucherDetails_1.RoomID = Room_1.RoomID LEFT OUTER JOIN
                      dbo.Rack AS Rack_1 ON VoucherDetails_1.RackID = Rack_1.RackID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON VoucherDetails_1.AccountNo = Accounts_1.AccountNo ON Voucher_1.VoucherID = VoucherDetails_1.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo AS SessionInfo_1 ON Voucher_1.SessionID = SessionInfo_1.SessionID
WHERE     (VoucherDetails_1.DrOrCr = N'Cr')

-----------------------------------------------------------

USE [BSC]
GO
/****** Object:  View [dbo].[vw_LotWise]    Script Date: 01/08/2020 13:57:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_LotWise]
AS
SELECT     VoucherDetailID, ISNULL(LotNo, '-') AS Lot_No, ISNULL(Marka, '-') AS Marka, StoreLot AS Store_Lot, ItemID, ItemName AS Item_Name, RackID, RackName AS Rack, 
                      ManzalID, ManzalName AS Manzal, RoomID, RoomName AS Room, BankAccountID, BankAccountTitle AS Party_Name, VoucherDate AS in_Date, VoucherNo AS in_No, 
                      ISNULL(LotNo, '-') + ' ' + ISNULL(Marka, '-') + ' ' + StoreLot AS Search, Qty AS InQty, 0 AS OutQty
FROM         dbo.vw_VouchersDetail
WHERE     (ItemID IS NOT NULL) AND (VoucherNo LIKE 'AI-%')
UNION ALL
SELECT     VDID AS VoucherDetailID, ISNULL(LotNo, '-') AS Lot_No, ISNULL(Marka, '-') AS Marka, StoreLot AS Store_Lot, ItemID, ItemName AS Item_Name, RackID, 
                      RackName AS Rack, ManzalID, ManzalName AS Manzal, RoomID, RoomName AS Room, BankAccountID, BankAccountTitle AS Party_Name, VoucherDate AS in_Date, 
                      VoucherNo AS in_No, ISNULL(LotNo, '-') + ' ' + ISNULL(Marka, '-') + ' ' + StoreLot AS Search, 0 AS InQty, Qty AS OutQty
FROM         dbo.vw_VouchersDetail AS vw_VouchersDetail_1
WHERE     (ItemID IS NOT NULL) AND (VoucherNo LIKE 'OI-%')

GO